<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of photo
 *
 * @author Andi
 */
include_once("database.php");
include_once 'notification.php';

class photo extends  database {
    //put your code here
    var $table = 'photo';
    var $col_id = 'photo_id';

    function  __construct() {
        $this->connect();
    }


    function showPhotoID($userName,$urlPhoto){
        $query = "SELECT * FROM photo WHERE user_name = '$userName' AND url_photo = '$urlPhoto'";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result);
        $photoID = $row['photo_id'];
        return $photoID;        
    }

    function uploadProfPict($user_name,$url_photo){
        if (!is_dir("images/photos/".$user_name)){
            mkdir("images/photos/".$user_name);
        }
        $date_now = date('Y-m-d');
        $target_path = "images/photos/" . $user_name . "/" . $url_photo['name']; 
        $sql = "INSERT INTO photo (user_name,post_date,url_photo) VALUE ('$user_name','$date_now','$target_path')";
        if (move_uploaded_file($url_photo['tmp_name'], $target_path)){
            mysql_query($sql);
        }
    }

    function upload($user_name,$photo_desc,$key_word ,$capt_date,$category,$photo_prev,$photo_com_prev,$url_photo){
        if (!is_dir("images/photos/" . $user_name)){
            mkdir("images/photos/" . $user_name);
        }
        $date_now = date('Y-m-d');
        $target_path = "images/photos/" . $user_name . "/" . basename($url_photo['name']);
        $sql = "INSERT INTO photo (user_name,photo_desc,key_word ,capt_date,post_date,category,photo_prev,photo_com_prev,url_photo) VALUE ('$user_name','$photo_desc','$key_word','$capt_date','$date_now','$category','$photo_prev','$photo_com_prev','$target_path')";
        if (move_uploaded_file($url_photo['tmp_name'], $target_path)){
            mysql_query($sql);        
        }
    }

    function editPhoto($pil,$userName,$urlPhoto,$new_val){// 1->photo_desc,2->key_word ,3->capt_date,4->post_date,5->category,6->photo_prev,7->photo_com_prev,8->url_photo
        $id = $this->showPhotoID($userName, $urlPhoto);
        if ($pil == 1){
            $column_change = 'photo_desc';
            $this->update($this->table, $this->col_id, $id, $column_change, $new_val);
        }else if ($pil == 2) {
            $column_change = 'key_word';
            $this->update($this->table, $this->col_id, $id, $column_change, $new_val);
        }else if ($pil == 3) {
            $column_change = 'capt_date';
            $this->update($this->table, $this->col_id, $id, $column_change, $new_val);
        }else if ($pil == 4) {
            $column_change = 'post_date';
            $this->update($this->table, $this->col_id, $id, $column_change, $new_val);
        }else if ($pil == 5) {
            $column_change = 'category';
            $this->update($this->table, $this->col_id, $id, $column_change, $new_val);
        }else if ($pil == 6) {
            $column_change = 'photo_prev';
            $this->update($this->table, $this->col_id, $id, $column_change, $new_val);
        }else if ($pil == 7) {
            $column_change = 'photo_com_prev';
            $this->update($this->table, $this->col_id, $id, $column_change, $new_val);
        }else if ($pil == 8) {
            $column_change = 'url_photo';
            $this->update($this->table, $this->col_id, $id, $column_change, $new_val);
        }else{}
    }

    function deletePhoto($userName, $urlPhoto){
        $photoID = $this->showPhotoID($userName, $urlPhoto);
        $this->delete($this->table, $this->col_id, $photoID);
        $this->delete('photo_comment', $this->col_id, $photoID);
        $this->delete('photo_tag', $this->col_id, $photoID);
        $this->delete('photo_value', $this->col_id, $photoID);
    }

    function addPhotoCom($userName,$urlPhoto,$user_name_com,$photo_com_body){
        $photoID = $this->showPhotoID($userName, $urlPhoto);
        $sql = "INSERT INTO photo_comment (photo_id,user_name,photo_com_body) VALUE ('$photoID','$user_name_com','$photo_com_body')";
        mysql_query($sql);
    }

    function deletePhotoCom($userName,$urlPhoto,$user_name_com,$photo_com_body){
        $photo_id = $this->showPhotoID($userName, $urlPhoto);
        $sql = "DELETE FROM photo_comment WHERE photo_id = '$photo_id' AND photo_com_body = '$photo_com_body'";
        $result = mysql_query($sql);
    }

    function addPhotoTag($userName,$urlPhoto,$user_name_tag){
        $photoID = $this->showPhotoID($userName, $urlPhoto);
        $sql = "INSERT INTO photo_tag (photo_id,user_name) VALUE ('$photoID','$user_name_tag')";
        mysql_query($sql);
        $notif = new notification();
        $notifBody = $userName . ' has tagged you' ;
        $notif->addNotif($user_name_tag, $notifBody);
    }

    function addPhotoVal($userName,$urlPhoto,$user_name_val,$value){
        $photoID = $this->showPhotoID($userName, $urlPhoto);
        $sql = "INSERT INTO photo_value (photo_id,user_name,value) VALUE ('$photoID','$user_name_val','$value')";
        mysql_query($sql);
    }

    function showPhoto($userName, $urlPhoto){
        $photoID = $this->showPhotoID($userName, $urlPhoto);
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        return $result;
    }

    function showPhotoByKeyword($keyword){
        $sql = "SELECT * FROM photo WHERE key_word = '$keyword'";
        $result = mysql_query($sql);
        return $result;
    }

    function showPhotoByKeywordFiltered($keyword,$hakAkses){
        $sql = "SELECT * FROM photo WHERE key_word = '$keyword' AND photo_prev='$hakAkses'";
        $result = mysql_query($sql);
        return $result;
    }

    function showPhotoByName($user_name){
        $sql = "SELECT * FROM photo WHERE user_name = '$user_name'";
        $result = mysql_query($sql);
        return $result;
    }

    function showPhotoCom($userName, $urlPhoto){
        $photoID = $this->showPhotoID($userName, $urlPhoto);
        $sql = "SELECT photo_comment.user_name AS user_name,photo_com_body AS body FROM photo_comment,photo WHERE photo_comment.photo_id = photo.photo_id AND photo_comment.photo_id = '$photoID'";
        $result = mysql_query($sql);
        return $result;
    }

    function showPhotoTag($userName, $urlPhoto){
        $photoID = $this->showPhotoID($userName, $urlPhoto);
        $sql = "SELECT * FROM photo_tag WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        return $result;
    }

    function showSumPhotoVal($userName, $urlPhoto){
        $photoID = $this->showPhotoID($userName, $urlPhoto);
        $sql = "SELECT * FROM photo_value WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $sum = 0;
        while ($row = mysql_fetch_array($result)){
            $sum = $sum + $row['value'];
        }
        return $sum;
    }

    function showSumPhotoValByID($photoID){
        $sql = "SELECT * FROM photo_value WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $sum = 0;
        while ($row = mysql_fetch_array($result)){
            $sum = $sum + $row['value'];
        }
        return $sum;
    }

    function showAvrgPhotoVal($userName, $urlPhoto){
        $photoID = $this->showPhotoID($userName, $urlPhoto);
        $sql = "SELECT * FROM photo_value WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $sum = 0;
        $totRow = mysql_num_rows($result);
        while ($row = mysql_fetch_array($result)){
            $sum = $sum + $row['value'];
        }
        $avrg = $sum/$totRow;
        return $avrg;
    }

    function getUserName($photoID){
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $user_name = $row['user_name'];
        return $user_name;
    }

    function getPhotoDesc($photoID){
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $photo_desc = $row['photo_desc'];
        return $photo_desc;
    }

    function getKeyWord($photoID){
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $key_word = $row['key_word'];
        return $key_word;
    }

    function getCaptDate($photoID){
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $capt_date = $row['capt_date'];
        return $capt_date;
    }

    function getPostDate($photoID){
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $post_date = $row['post_date'];
        return $post_date;
    }

    function getCategory($photoID){
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $category = $row['category'];
        return $category;
    }

    function getPhotoPrev($photoID){
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $photo_prev = $row['photo_prev'];
        return $photo_prev;
    }

    function getPhotoComPrev($photoID){
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $photo_com_prev = $row['photo_com_prev'];
        return $photo_com_prev;
    }

    function getUrlPhoto($photoID){
        $sql = "SELECT * FROM photo WHERE photo_id = '$photoID'";
        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $url_photo = $row['url_photo'];
        return $url_photo;
    }

    function showLastUploadedPhoto(){
        $sql = "SELECT * FROM photo ORDER BY photo_id DESC";
        $result = mysql_query($sql);
        return $result;
    }

    function isHasScored($photoID,$userName){
        $sql = "SELECT * FROM photo_value WHERE photo_id = '$photoID' AND user_name = '$userName'";
        $result = mysql_query($sql);
        if(!$result)
            {echo'lontong sapi';}
        $count = mysql_num_rows($result);
        if ( $count == 0){
            return false;
        }else{
            return true;
        }
    }

    function showMostPopularPhoto(){
        $sql = 'SELECT photo.user_name,photo.photo_id,photo_prev,url_photo FROM `photo_comment`,photo where photo.photo_id = photo_comment.photo_id group by photo_comment.photo_id order by count(photo_comment.photo_id) desc';
        $result = mysql_query($sql);
        return $result;
    }

    function showMostValuePhoto(){
        $query = 'SELECT photo.user_name,photo.photo_id,photo_prev,url_photo,sum(photo_value.value) FROM `photo_value`,photo where photo.photo_id = photo_value.photo_id group by photo_value.photo_id order by sum(photo_value.value) desc';
        $result = mysql_query($query);
        return $result;
    }

}
?>
